#
# Copyright 2008 Spencer Davis <SpencerDavis91@gmail.com>
# Copyright 2008 Charles Connell <charles@connells.org>
#
# This file is part of Recuerde.
#
# Recuerde is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# Recuerde is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with Recuerde; if not, see <http://gnu.org/licenses/.>
#

import os
import sys
import wx
import recuerde
from recuerde import List
from recuerde import Item
from sqlite3 import dbapi2 as sqlite

# Set constant for the databse location
DBPATH = os.path.join(os.path.expanduser('~'), '.recuerde')

class Database:
    """Interface to multi-table database that stores lists of items."""
    def __init__(self):
        """Initialize database interface"""

        # Create a cursor for the datbase
        cursor = sqlite.connect(DBPATH).cursor()

        # Try to execute a query to make sure that the database exists
        try:
            cursor.execute('SELECT * FROM recuerde_master')

        # If there was a problem with the database, create the master table
        except sqlite.OperationalError:
            # Try to create the table
            try:
                queryString = 'CREATE TABLE recuerde_master(LIST TEXT, STRING TEXT, DATE TEXT, CHOICE TEXT)'
                print queryString
                cursor.execute(queryString)

            # If there was an error, display an error dialog
            except sqlite.Error, message:
                print message.message

        # Create empty list that will be filled with List objects.
        self.lists = []

    def ReadTasks(self):
        """Reads all of the lists from the database and stores them in a list"""

        # Empty list
        del self.lists[:]

        # Create a cursor for the database
        cursor = sqlite.connect(DBPATH).cursor()

        # Get list of all tables in database
        try:
            cursor.execute("SELECT list,string,date,choice FROM recuerde_master")
            tables = cursor.fetchall()
        except sqlite.Error, message:
            print message.message

        # Loop through each row of the result. Each row has information that describes a list
        # in Recuerde, and refers us to another table with the actual items stored in it.
        for table in tables:
            l = List()
            self.lists.append(l)

            # Set name from first column of query
            l.SetName(table[0])

            # Add String fields from second column of query, they are separared by semi-colon
            # Notice that we remove the last character, a trailing semi-colon
            for field in table[1].split(';')[:-1]:
                l.AddField(recuerde.FIELDTYPE_STRING, field)

            # Add Date fields from third column of query, they are separared by semi-colon
            for field in table[2].split(';')[:-1]:
                l.AddField(recuerde.FIELDTYPE_DATE, field)

            # Add Choice fields from fourth column of query, they are separared by semi-colon
            for field in table[3].split(';')[:-1]:
                # Each field has a string with comma-separated values, split those into a list
                choices = field.split(',')
                # The first comma-separated value is the name of the field, grab that
                field = choices[0]
                # Remove the name from the list, now it just has the choices
                del choices[0]
                # Add the field to the list
                l.AddField(recuerde.FIELDTYPE_CHOICE, field, tuple(choices))

            # Assemble string the with names of the columns in current list's table
            # First field is NAME
            fieldsString = 'NAME,'
            # Other columns have the names of the fields in the list
            for field in l.GetFields():
                fieldsString += field[1] + ','
            # Remove trailing comma
            fieldsString = fieldsString[:-1]

            # Query all the columns in fieldsString, for every row, in the table for this list
            try:
                cursor.execute("SELECT %s FROM %s" % (fieldsString, table[0]))
                items = cursor.fetchall()
            except sqlite.Error, message:
                print message.message
            # Each row of this query represents an item in the current list
            for item in items:
                # Create the item that we will fill with data, and add it to the current list
                i = Item()
                l.AddItem(i)
                # Set the item name from the first column of the result
                i.SetName(item[0])
                # Loop through each other column AND each field in this list simultaneously, because they correspond to each other
                for value, field in zip(item[1:], l.GetFields()):
                    # If field type is string, add its value to the item
                    if field[0] is recuerde.FIELDTYPE_STRING:
                        i.SetProperty(field[1], value)
                    # If field type is string, add its value to the item
                    elif field[0] is recuerde.FIELDTYPE_DATE:
                        date = wx.DateTime()
                        # Convert from text to wx.DateTime
                        date.ParseDate(value)
                        i.SetProperty(field[1], date)
                    # If field type is choice, add its value to the item
                    elif field[0] is recuerde.FIELDTYPE_CHOICE:
                        i.SetProperty(field[1], value)

    def SaveItem(self, item, oldItemName):
        """Takes an Item object and saves it to the database"""
        # Connect to the database
        conn = sqlite.connect(DBPATH)

        # Prepare string with "COLUMN = VALUE" phrases separated by commas
        # All strings must have apostraphes (aka single-quotes) escaped to a double-apostraphe before they are put in a query
        # First one is name
        newValuesString = "NAME = '%s'" % oldItemName.replace("'", "''")
        # The rest are the fields and their values
        for field in item.GetList().GetFields():
            newValuesString += ", %s = '%s'" % (field[1], item.GetProperty(field[1]).__str__().replace("'", "''"))

        # Try to execute the query to update the record of the item
        try:
            queryString = "UPDATE %s SET %s WHERE NAME = '%s';" % (item.GetList().GetName().replace("'", "''"), newValuesString, oldItemName.replace("'", "''"))
            print queryString
            conn.execute(queryString)
            # Write the new data to the database file
            conn.commit()
        except sqlite.Error, message:
            print message.message

    def AddItem(self, item, list):
        """Takes an Item object and saves it to the database"""
        # Connect to the database
        conn = sqlite.connect(DBPATH)

        # Add item to list in memory
        list.AddItem(item)

        # All strings must have apostraphes (aka single-quotes) escaped to a double-apostraphe before they are put in a query
        # Create comma-separated list of the values in the item
        valuesString = "'%s'" % item.GetName().replace("'", "''")
        for field in list.GetFields():
            valuesString += ", '%s'" % (item.GetProperty(field[1]).__str__().replace("'", "''"))

        # Create comma-separated list of the fields in the item. These correspond to the order of the list of values.
        fieldsString = "'NAME'"
        for field in list.GetFields():
            fieldsString += ", '%s'" % (field[1].replace("'", "''"))

        # Try to execute the query to insert the item
        try:
            queryString = 'INSERT INTO %s (%s) VALUES (%s);' % (list.GetName().replace("'", "''"), fieldsString, valuesString)
            print queryString
            conn.execute(queryString)
            # Write the new data to the database file
            conn.commit()
        except sqlite.Error, message:
            print message.message

    def RemoveItem(self, item, list):
        """Removes an Item object from the database."""
        conn = sqlite.connect(DBPATH)

        # Remove the item from the list in memroy
        list.RemoveItem(item)

        # Try to execute the query to remove the item
        try:
            # All strings must have apostraphes (aka single-quotes) escaped to a double-apostraphe before they are put in a query
            queryString = "DELETE FROM %s WHERE NAME = '%s';" % (item.GetList().GetName().replace("'", "''"), item.GetName().replace("'", "''"))
            print queryString
            conn.execute(queryString)
            # Write the data to the database file
            conn.commit()
        except sqlite.Error, message:
            print message.message

    def AddList(self, newList):
        """Adds a List object to the database."""
        conn = sqlite.connect(DBPATH)

        # Add List to list of List's in memory
        self.lists.append(newList)

        # We will be making a new table for this list
        # Prepare string listing the name of each of the columns we want in our new table, followed by the SQL type (always TEXT)
        # The first is NAME
        fieldsString = 'NAME TEXT, '
        # The rest are named after the fields in the list
        for field in newList.GetFields():
            fieldsString += field[1] + ' TEXT, '

        # Try to execute query to create new table
        try:
            # All strings must have apostraphes (aka single-quotes) escaped to a double-apostraphe before they are put in a query
            queryString = "CREATE TABLE '%s' (%s);" % (newList.GetName().replace("'", "''"), fieldsString[:-2])
            print queryString
            conn.execute(queryString)
        except sqlite.Error, message:
            print message.message

        # Prepare strings that will have semi-colon-separate lists of the fields of each type in them
        stringString = ''
        dateString = ''
        choiceString = ''

        # Go through each field
        for field in newList.GetFields():
            # Append it if it is of type string
            if field[0] == recuerde.FIELDTYPE_STRING:
                stringString += field[1] + ';'
            # Append it if it is of type date
            elif field[0] == recuerde.FIELDTYPE_DATE:
                dateString += field[1] + ';'
            # Append it if it is of type choice
            elif field[0] == recuerde.FIELDTYPE_CHOICE:
                # Append list of choices separated by commas
                choiceString += field[1] + ',' + ','.join(field[2]) + ';'

        # Create commas-separated string of the four values for column, representing each type
        valuesString = "'%s', '%s', '%s', '%s'" % (newList.GetName().replace("'", "''"), stringString.replace("'", "''"), dateString.replace("'", "''"), choiceString.replace("'", "''"))

        # Try query to create record in recuerde_master of new list
        try:
            queryString = 'INSERT INTO recuerde_master (LIST, STRING, DATE, CHOICE) VALUES (%s);' % valuesString
            print queryString
            conn.execute(queryString)
            # Write new data to database file
            conn.commit()
        except sqlite.Error, message:
            print message.message

    def RemoveList(self, list):
        """Removes list and its items from database."""
        conn = sqlite.connect(DBPATH)

        # Remove list from memory
        self.lists.remove(list)

        # Try query to remove row from recuerde_master
        try:
            # All strings must have apostraphes (aka single-quotes) escaped to a double-apostraphe before they are put in a query
            queryString = "DELETE FROM recuerde_master WHERE LIST = '%s';" % list.GetName().replace("'", "''")
            print queryString
            conn.execute(queryString)
        except sqlite.Error, message:
            print message.message

        # Try query to drop table with list items in it
        try:
            queryString = "DROP TABLE '%s'" % list.GetName().replace("'", "''")
            print queryString
            conn.execute(queryString)
            # Write to database file
            conn.commit()
        except sqlite.Error, message:
            print message.message

    def GetLists(self):
        """Return list of List objects that reflects the contents of the database."""
        return self.lists

